package SqlDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class SqlDB {

	private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	
	  // 连接路径
	private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=yngsxy";
	
	private static Statement stmt=null; 
	
	private static Connection conn = null;
	
	private static ResultSet rs=null;
	
	private static final String user= "sa";
	
	private static final String password="123456";
	
	MD5Encode encode=new MD5Encode();
	
	//数据库的链接
	public static Connection GetCon() throws SQLException {
		
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	
			try {
				conn = (Connection) DriverManager.getConnection(URL,user,password);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return conn;		
	}
	
	//查询数据库
	public Integer GetAll() throws SQLException {
		
		Connection con=GetCon();
		String sql="SELECT * FROM Users";
		
		try {
			   stmt=(Statement) ((java.sql.Connection) conn).createStatement();
			   rs = ((java.sql.Statement) stmt).executeQuery(sql);
			   
			   while(rs.next()){
				   int i = 1;
				   String id=rs.getString(i++);
				   String name=rs.getString(i++);
				   String pwd=rs.getString(i++);
				   int role=rs.getInt(i++);
				   String depict=rs.getString(i++);
				   
				   System.out.println("编号："+id+"姓名："+name+"密码："+encode.MD5Encode(pwd)+"角色："+role+"说明："+depict);

			   }
			   
			   
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;		
	}

	//插入数据
	public static int Insert(User user) throws SQLException {
		
		Connection con=GetCon();
		String sql="insert into Users (LoginID,Name,Pwd,Role,Depict) values(?,?,?,?,?)";
		int i=0;
		PreparedStatement pstmt;
		    try {
		        pstmt = (PreparedStatement)conn.prepareStatement(sql);
		        pstmt.setString(1,user.getLoginID());
		        pstmt.setString(2,user.getName() );
		        pstmt.setString(3,user.getPwd() );
		        pstmt.setInt(4,user.getRole() );
		        pstmt.setString(5,user.getDepict());
		        i = pstmt.executeUpdate();
		        pstmt.close();
		        conn.close();
		    } catch (SQLException e) {
		        e.printStackTrace();
		    }
		    return i;
		
	}
	
	//修改数据
	public static int Update(User user) throws SQLException {
			
			Connection con=GetCon();
			String sql="update Users set Name='" + user.getName() + "' where LoginID='" + user.getLoginID() + "'";
			int i=0;
			 PreparedStatement pstmt;
			    try {
			        pstmt = (PreparedStatement) conn.prepareStatement(sql);
			        i = pstmt.executeUpdate();
			        System.out.println("result:"+i);
			        pstmt.close();
			        conn.close();
			    } catch (SQLException e) {
			        e.printStackTrace();
			    }
			    return i;
			
		}
	
	//删除数据
		public static int Delete(String loginId) throws SQLException {
				
				Connection con=GetCon();
				String sql="delete from Users where LoginID='" + loginId + "'";
				int i=0;
				 PreparedStatement pstmt;
				    try {
				        pstmt = (PreparedStatement) conn.prepareStatement(sql);
				        i = pstmt.executeUpdate();
				        System.out.println("result:"+i);
				        pstmt.close();
				        conn.close();
				    } catch (SQLException e) {
				        e.printStackTrace();
				    }
				    return i;
				
			}
		
		public static void main(String[] args) throws SQLException {
			SqlDB sqldb=new SqlDB();
			sqldb.GetAll();
			sqldb.Insert(new User("1009","Achilles", "Male",1, "学生"));
			sqldb.GetAll();
			sqldb.Update(new User("1009", "dada", "", 1, ""));
			sqldb.GetAll();
			sqldb.Delete("1008");
			sqldb.GetAll();
		}
	
}

